﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Serialization;
using DbUp.Helpers;
using System.Runtime.InteropServices;
using DbUp.ScriptProviders;

namespace DbUp.Journal
{
    /// <summary>
    /// An implementation of the <see cref="IJournal"/> interface which tracks version numbers for a 
    /// SQL Server database using a table called dbo.SchemaVersions.
    /// </summary>
    public sealed class TableJournal : IJournal
    {
        private readonly string tableName;
        private readonly bool _maskSchemaTableAsSystemTable;
        private readonly string schemaName;
        private readonly string schemaTableName;
        private readonly string dbConnectionString;
        private readonly ILog   log;

        private readonly HashSet<string> _connectionStringInstanceInitialized = new HashSet<string>(StringComparer.OrdinalIgnoreCase);


        /// <summary>
        /// Initializes a new instance of the <see cref="TableJournal"/> class.
        /// </summary>
        public TableJournal(string targetDbConnectionString) : this(targetDbConnectionString, "dbo", "SchemaVersions", true, new ConsoleLog())
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="TableJournal"/> class.
        /// </summary>
        /// <param name="targetDbConnectionString">The connection to the target database.</param>
        /// <param name="schema">The schema that contains the table.</param>
        /// <param name="table">The table name.</param>
        /// <param name="maskSchemaTableAsSystemTable">Marks the </param>
        /// <param name="logger">The log.</param>
        /// <example>
        /// var journal = new TableJournal("Server=server;Database=database;Trusted_Connection=True;", "dbo", true, "MyVersionTable");
        /// </example>
        public TableJournal(string targetDbConnectionString, string schema, string table, bool maskSchemaTableAsSystemTable, ILog logger)
        {
            this.schemaName = schema;
            this.tableName = table;
            dbConnectionString = targetDbConnectionString;
            _maskSchemaTableAsSystemTable = maskSchemaTableAsSystemTable;
            schemaTableName = schemaName + "." + tableName;
            log = logger;
        }

        /// <summary>
        /// Recalls the version number of the database.
        /// </summary>
        /// <returns></returns>
        public IEnumerable<SqlScriptAlreadyApplied> GetExecutedScripts()
        {
            log.WriteInformation("Fetching list of already executed scripts.");
            var returnItems = new List<SqlScriptAlreadyApplied>();
            var adHocSqlRunner = new AdHocSqlRunner(dbConnectionString);

            var exists = DoesTableExist(adHocSqlRunner);
            if (!exists)
            {
                log.WriteInformation(string.Format("The {0} table could not be found. The database is assumed to be at version 0.", schemaTableName));
                return returnItems;
            }

            adHocSqlRunner.ExecuteReader(
                string.Format(
                    "select VersionGuid, ScriptName, AppliedByUser, AppliedDate, ServerName from {0} order by AppliedDate",
                    schemaTableName)
                ).ForEach(row =>
                              {
                                  var item = new SqlScriptAlreadyApplied(
                                      row.ParseIt(VersionGuid => new Guid(VersionGuid.ToString())),
                                      row.ParseIt(ScriptName => ScriptName as string),
                                      row.ParseIt(AppliedByUser => AppliedByUser as string),
                                      row.ParseIt(AppliedDate => DateTime.Parse(AppliedDate as string)),
                                      row.ParseIt(ServerName => ServerName as string)
                                      );
                                  returnItems.Add(item);
                              });

            return returnItems;
        }

        /// <summary>
        /// Records a database upgrade for a database specified in a given connection string.
        /// </summary>
        /// <param name="script">The script.</param>
        public void StoreExecutedScript(SqlScript script)
        {
            var adHocSqlRunner = new AdHocSqlRunner(dbConnectionString);

            if (!_connectionStringInstanceInitialized.Contains(dbConnectionString))
            {
                InitializeVersionSchemaTable(adHocSqlRunner, log);

                _connectionStringInstanceInitialized.Add(dbConnectionString);
            }

            var sql = string.Format("insert into {0} (VersionGuid, ScriptName) values (@versionGuid, @scriptName)", schemaTableName);
            adHocSqlRunner.ExecuteNonQuery(sql,
                scriptName => script.Name,
                versionGuid => script.VersionKey);
        }

        private bool DoesTableExist(AdHocSqlRunner adHocSqlRunner)
        {
            var sql = string.Format("select count(*) from sys.objects where type='U' and name='{0}'", tableName);
            int result;
            int.TryParse(adHocSqlRunner.ExecuteScalar(sql).ToString(), out result);

            if (result == 0)
            {
                return false;
            }
            return true;
        }

        private void InitializeVersionSchemaTable(AdHocSqlRunner adHocSqlRunner, ILog log)
        {
            var exists = DoesTableExist(adHocSqlRunner);
            if (!exists)
            {
                log.WriteInformation(string.Format("Creating the {0} table", schemaTableName));

                string sql = string.Format(
@"CREATE TABLE [{0}].[{1}](
	[VersionGuid]	uniqueidentifier																		NOT NULL PRIMARY KEY CLUSTERED,
	[ScriptName]	VARCHAR(2000)																			NULL,
	[AppliedByUser] VARCHAR (500)		DEFAULT (SUSER_SNAME())												NOT NULL,
	[AppliedDate]	DATETIME			DEFAULT (GETUTCDATE())												NOT NULL,
	[ServerName]    VARCHAR (500)		DEFAULT (CONVERT (VARCHAR (500), serverproperty('SERVERNAME'), 0))	NOT NULL
);" + (_maskSchemaTableAsSystemTable?
@"
-- Hide the table so tools such as DataDude won't see it.
EXEC sys.sp_addextendedproperty @name=N'microsoft_database_tools_support', @value=N'DbUp schema change log' , @level0type=N'SCHEMA',@level0name=N'{0}', @level1type=N'TABLE',@level1name=N'{1}'
" : string.Empty), schemaName, tableName);

                adHocSqlRunner.ExecuteNonQuery(sql);


                log.WriteInformation(string.Format("The {0} table has been created", schemaTableName));
            }
        }
    }

    public static class Extensions
    {


        public static T ParseIt<T>(this IDictionary<string, object> reader, Func<object, T> parser)
        {
            var name = parser.Method.GetParameters().First().Name;

            if (!reader.ContainsKey(name))
                throw new ArgumentException(string.Format("Could not find key[{0}] in list of keys[{1}]", name, string.Join(", ", reader.Keys.ToArray())));

            object v = reader[name];
            try
            {
                T value;
                if (v == DBNull.Value)
                {
                    value = parser(null);
                }
                else
                {
                    value = parser(v);
                }
                return value;
            }
            catch (Exception ex)
            {

                throw new DataReaderParseException(name, typeof(T), v, ex);
            }
        }
    }


    public class DataReaderParseException : Exception
    {
        public DataReaderParseException(string name, Type expectedType, object value, Exception inner)
            : base(string.Format("Could not parse type {0}:[{1}] into {2}", name, (value == null ? "null" : value.GetType().FullName), expectedType.FullName), inner)
        {
        }
    }
}
